"""
看前一天持续流入资金的票。在今天表现咋样
"""

import time, datetime
import pymysql
import pandas as pd

pd.set_option('display.max_columns', 100)
pd.set_option('display.width', 500)

# 忽略警告
import warnings

warnings.filterwarnings("ignore")


def change_money_unit(money):
    """将金钱的单位从元改为xx亿或xx万，方便阅读"""
    if abs(money) >= 100000000:
        return f'{round(money / 100000000, 2)}亿'
    elif abs(money) >= 10000:
        return f'{round(money / 10000, 2)}万'
    else:
        return money


def change_unit(num):
    if num[-1] == '亿':
        return float(num[:-1]) * 100000000
    elif num[-1] == '万':
        return float(num[:-1]) * 10000
    elif num == '' or num == ' ' or num == '-':
        return 0
    else:
        return int(num[:-3])


"""
获取指定代码的最近资金和涨跌幅信息
"""

df_latest = pd.read_excel('data/df_code_continue_latest_2021-09-29_190517_517.xlsx')
# 修复代码，左边填充0
df_latest['代码'] = df_latest['代码'].map(lambda x: str(x).zfill(6))
# 提出股票代码清单
code_list = df_latest['代码'].drop_duplicates().to_list()

# 链接mysql
conn = pymysql.connect(host='localhost', user='root', passwd='root', db='stock', charset="utf8")
cursor = conn.cursor()
sql = f"""
SELECT `code`,name,day_no,price,change_rate,main_money_num,main_money_rate 
FROM stock.`stock_history_money_d` 
WHERE price>0 and day_no=DATE_SUB(CURDATE(),INTERVAL 0 DAY)
and code in ({'"'+'","'.join(code_list)+'"'})
"""
# 获取所有的股票资金信息
df_code_today = pd.read_sql(sql, conn)
cursor.close()
conn.close()

df_latest_today=pd.merge(df_latest,df_code_today,left_on='代码',right_on='code',how='left')
del df_latest_today['code']
del df_latest_today['name']
del df_latest_today['main_money_rate']
df_latest_today['今日涨跌幅'] = df_latest_today['change_rate'].map(lambda x: f'{x}%')
df_latest_today['今日主力资金'] = df_latest_today['main_money_num'].apply(change_money_unit)
del df_latest_today['change_rate']
del df_latest_today['main_money_num']

df_latest_today.to_excel(f'data/df_code_continue_latest_check_{str(datetime.date.today())}_{datetime.datetime.now().strftime("%H%M%S")}_{int(time.time()) % 1000}.xlsx', index=False)
